﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

using log4net;
using log4net.Config;
using System.Reflection;

namespace AutoRule
{
    class CDB
    {
        private static CDB _instance;
        private CDB()
        {
        
        }
        public static CDB GetInstance()
        {
            if (_instance == null)
            {
                _instance = new CDB();
            }
            return _instance;
        }

        public void Init(String ip, String dbname, String user, String pwd)
        {
            _adress = string.Format("UID={0};Password={1};Initial Catalog={2};Data Source={3};min pool size=7", user, pwd, dbname, ip);
        }

        public List<CRegRule> ParseRegMainRule()
        {
            List<CRegRule> listReg = new List<CRegRule>();
            using (SqlConnection connection = new SqlConnection(_adress))
            {
                // Create the Command and Parameter objects.
                SqlCommand command = new SqlCommand("SELECT a.id as ruleid, a.name, a.devtype, a.hours, b.ID as varid, b.GroupName as devname from T_MAINTIANCE_RegMain a INNER JOIN t_zutai_variables b on  a.devtype = b.GroupType where a.active = 1 and b.VaribleName ='运行数据DSR_整机运行实际小时'", connection);
                //command.Parameters.AddWithValue("@pricePoint", paramValue);

                // Open the connection in a try/catch block. 
                // Create and execute the DataReader, writing the result
                // set to the console window.
                try
                {
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        CRegRule item = new CRegRule();
                        item.id = Convert.ToInt32(reader["ruleid"]);
                        item.name = reader["name"].ToString();
                        item.DevType = reader["devtype"].ToString();
                        item.Hours = Convert.ToInt32(reader["hours"]);
                        item.varid = Convert.ToInt32(reader["varid"]);
                        item.DevName = reader["devname"].ToString(); ;
                        listReg.Add(item);
                    }
                    reader.Close();
                }
                catch (Exception ex)
                {
                    ILog log = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
                    log.Error("ParseRegMainRule", ex);

                }
                }

                return listReg;
        }

        public List<COffRule> GetOffsetRules()
        {
            List<COffRule> listReg = new List<COffRule>();
            using (SqlConnection connection = new SqlConnection(_adress))
            {
                // Create the Command and Parameter objects.
                SqlCommand command = new SqlCommand("SELECT a.id as ruleid, a.name as rulename, b.VaribleName as varname, a.offset as offsetvalue, a.devtype as devicetype, b.GroupName as devname, b.id as varid from T_MAINTAINCE_EXPWAR_CFG a inner JOIN t_zutai_variables b on a.devtype = b.GroupType  where a.type=1 and b.VaribleName = '运行数据WSR_有功功率'  and a.active =1", connection);
                try
                {
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        COffRule item = new COffRule();

                        item.ruleid = Convert.ToInt32(reader["ruleid"]);
                        item.RuleName = reader["rulename"].ToString();
                        item.DevType = reader["devicetype"].ToString();
                        item.DevName = reader["devname"].ToString();
                        item.offsetvalue = Convert.ToInt32(reader["offsetvalue"]);
                        item.varid = Convert.ToInt32(reader["varid"]);
                        item.VarName = reader["varname"].ToString();
                        listReg.Add(item);
                    }
                    reader.Close();
                }
                catch (Exception ex)
                {
                    ILog log = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
                    log.Error("GetOffsetRules", ex);
                }
            }

            return listReg;
        }


        public List<CLimitRule> GetLimitRules()
        {
            List<CLimitRule> listReg = new List<CLimitRule>();
            using (SqlConnection connection = new SqlConnection(_adress))
            {
                // Create the Command and Parameter objects.
                SqlCommand command = new SqlCommand("SELECT a.id as ruleid, a.name as rulename, a.uplimit as uplimit, a.downlimit as downlimit, a.devtype as devicetype, b.GroupName as devname, a.varname as varname, b.id as varid, a.issummer as issummer from T_MAINTAINCE_EXPWAR_CFG a inner JOIN t_zutai_variables b on a.devtype = b.GroupType and a.varname=b.VaribleName  where a.type=2  and a.active =1", connection);
                try
                {
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        CLimitRule item = new CLimitRule();

                        item.ruleid = Convert.ToInt32(reader["ruleid"]);
                        item.RuleName = reader["rulename"].ToString();
                        item.DevType = reader["devicetype"].ToString();
                        item.DevName = reader["devname"].ToString();
                        item.VarName = reader["varname"].ToString();
                        item.uplimit = Convert.ToInt32(reader["uplimit"]);
                        item.downlimit = Convert.ToInt32(reader["downlimit"]);
                        item.varid = Convert.ToInt32(reader["varid"]);
                        item.issummer = Convert.ToInt32(reader["issummer"]); 

                        listReg.Add(item);
                    }
                    reader.Close();
                }
                catch (Exception ex)
                {
                    ILog log = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
                    log.Error("GetLimitRules", ex);
                }
            }

            return listReg;
        }

        public List<CTimerRule> ParseTimerRule()
        {
            List<CTimerRule> listTimerRule = new List<CTimerRule>();
            using (SqlConnection connection = new SqlConnection(_adress))
            {
                // Create the Command and Parameter objects.
                SqlCommand command = new SqlCommand("select ID,Name,WeekDays,HourTimes,sys, IsUsed from T_Rule_Time where IsUsed = 1", connection);
                //command.Parameters.AddWithValue("@pricePoint", paramValue);

                // Open the connection in a try/catch block. 
                // Create and execute the DataReader, writing the result
                // set to the console window.
                try
                {
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        CTimerRule item = new CTimerRule(Convert.ToString(reader["WeekDays"]), Convert.ToString(reader["HourTimes"]), Convert.ToInt32(reader["ID"]), Convert.ToInt32(reader["sys"]));
                        listTimerRule.Add(item);


                    }
                    reader.Close();
                }
                catch (Exception ex)
                {
                    ILog log = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
                    log.Error("ParseTimerRule", ex);
                }
            }

            foreach (CTimerRule r1 in listTimerRule)
            {
                using (SqlConnection connection = new SqlConnection(_adress))
                {
                    // Create the Command and Parameter objects.
                    String Sql = "select T_Rule_Dev.ID as ruleid,T_Rule_Dev.DevName,T_Rule_Dev.Value,t_zutai_variables.GroupName,t_zutai_variables.ID,t_zutai_variables.VaribleName from T_Rule_Dev right join  t_zutai_variables  on T_Rule_Dev.DevName = t_zutai_variables.GroupName where T_Rule_Dev.ID = " + r1.id + " and t_zutai_variables.VaribleName = '整机开关BNX_整机开关'";
                    if(r1.sys == 1) //light
                         Sql = "select T_Rule_Dev.ID as ruleid,T_Rule_Dev.DevName,T_Rule_Dev.Value,t_zutai_variables.GroupName,t_zutai_variables.ID,t_zutai_variables.VaribleName from T_Rule_Dev right join  t_zutai_variables  on T_Rule_Dev.DevName = t_zutai_variables.ItemName where T_Rule_Dev.ID = " + r1.id;
                    SqlCommand command = new SqlCommand(Sql, connection);
                
                    try
                    {
                        connection.Open();
                        SqlDataReader reader = command.ExecuteReader();
                        while (reader.Read())
                        {
                            setinfo info = new setinfo();
                            info.varID = Convert.ToInt32(reader["ID"]);
                            info.varGroupName = Convert.ToString(reader["GroupName"]);
                            info.varName = Convert.ToString(reader["VaribleName"]);
                            info.varSetValue = Convert.ToInt32(reader["Value"]);
                            ILog log = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
                            
                            String logInfo = String.Format("详细详情:\t{0}\t{1}\t{2}\t{3}\t{4}", Convert.ToString(reader["ruleid"]), Convert.ToInt32(reader["ID"]),
                                Convert.ToString(reader["GroupName"]), Convert.ToString(reader["VaribleName"]), Convert.ToInt32(reader["Value"]));
                            log.Info(logInfo);
                            r1.lSetInfo.Add(info);
                        }
                        reader.Close();
                    }
                    catch (Exception ex)
                    {
                        ILog log = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
                        log.Error("ParseTimerRule", ex);
                    }
                }
            }

            return listTimerRule;
        }


            public void InsertRegMainRuleReport(int varid, String devname, int hours)
            {
                bool bHav = true;

                using (SqlConnection connection = new SqlConnection(_adress))
                {
                    // Create the Command and Parameter objects.
                    SqlCommand command = new SqlCommand("UPDATE T_MAINTIANCE_RegMainReport SET time=GETDATE() WHERE varid = @varid and hours=@hours", connection);
                    command.Parameters.AddWithValue("@varid", varid);
                    command.Parameters.AddWithValue("@hours", hours);
                    // Open the connection in a try/catch block. 
                    // Create and execute the DataReader, writing the result
                    // set to the console window.
                    try
                    {
                        connection.Open();
                        if (command.ExecuteNonQuery() < 1)
                            bHav = false;
                    }
                    catch (Exception ex)
                    {
                        ILog log = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
                        log.Error("InsertRegMainRuleReport", ex);
                    }
                }

                if (bHav)
                {
                    ILog log = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
                    log.Info("已存在该维护计划");
                    return;
                }
                using (SqlConnection connection = new SqlConnection(_adress))
                {
                    // Create the Command and Parameter objects.
                    SqlCommand command = new SqlCommand("INSERT into T_MAINTIANCE_RegMainReport(varid, devname,  HOURS, time, bhandle) VALUES (@varid,@devname, @hours,  GETDATE(), 0)", connection);
                    command.Parameters.AddWithValue("@varid", varid);
                    command.Parameters.AddWithValue("@devname", devname);
                    command.Parameters.AddWithValue("@hours", hours);
                    // Open the connection in a try/catch block. 
                    // Create and execute the DataReader, writing the result
                    // set to the console window.
                    try
                    {
                        connection.Open();
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        ILog log = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
                        log.Error("InsertRegMainRuleReport", ex);
                    }
                }
            }

            public void InsertTimerLog(int varID, String varName, String varGroup, int Value)
            {

                using (SqlConnection connection = new SqlConnection(_adress))
                {
                    // Create the Command and Parameter objects.
                    SqlCommand command = new SqlCommand("insert into T_Rule_RunHis (varID,varName,varGroup,Value,Time) values (@varid, @varName, @varGroup, @Value, GETDATE())", connection);
                    command.Parameters.AddWithValue("@varid", varID);
                    command.Parameters.AddWithValue("@varName", varName);
                    command.Parameters.AddWithValue("@varGroup", varGroup);
                    command.Parameters.AddWithValue("@Value", Value);
          
                    // Open the connection in a try/catch block. 
                    // Create and execute the DataReader, writing the result
                    // set to the console window.
                    try
                    {
                        connection.Open();
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        ILog log = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
                        log.Error("InsertTimerLog", ex);
                    }
                }
            }

            public void InsertExpWarRep(int ruleid, String DevName, String DevType, double avg, double offset, double uplimit, double downlimit, double value, int issummer, int ruletype, String varname)
            {

                using (SqlConnection connection = new SqlConnection(_adress))
                {
                    // Create the Command and Parameter objects.
                    SqlCommand command = new SqlCommand("INSERT INTO T_MAINTAINCE_EXPWAR_REP(ruleid, time, devname, devtype, avg, offset, uplimit, downlimit, value, issummer, ruletype, varname, active) VALUES (@ruleid, GETDATE(), @devname, @devtype, @avg, @offset, @uplimit, @downlimit, @value, @issummer, @ruletype, @varname, 0)", connection);
                    command.Parameters.AddWithValue("@ruleid", ruleid);
                    command.Parameters.AddWithValue("@devname", DevName);
                    command.Parameters.AddWithValue("@devtype", DevType);
                    command.Parameters.AddWithValue("@avg", avg);
                    command.Parameters.AddWithValue("@offset", offset);
                    command.Parameters.AddWithValue("@uplimit", uplimit);
                    command.Parameters.AddWithValue("@downlimit", downlimit);
                    command.Parameters.AddWithValue("@value", value);
                    command.Parameters.AddWithValue("@issummer", issummer);
                    command.Parameters.AddWithValue("@ruletype", ruletype);
                    command.Parameters.AddWithValue("@varname", varname);
                    // Open the connection in a try/catch block. 
                    // Create and execute the DataReader, writing the result
                    // set to the console window.
                    try
                    {
                        connection.Open();
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        ILog log = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
                        log.Error("InsertExpWarRep", ex);
                    }
                }
            }

        private String _adress;
        
    }

    
}
